import pdb
import os, sys
import sqlite3
import MySQLdb

class TDQueueSqlite:


    # param: path to sqlite file, table name
    def __init__(self, file, queue_name):
        self.sqlite_file = file
        self.queue_name  = queue_name

        # added file exists check
        # NOTE: sqlite3 auto creates!
        if self.sqlite_file != ':memory:' and not os.path.exists(self.sqlite_file):
            print "Error - sqlite file doesn't exists! - " + self.sqlite_file
            sys.exit(2)

        try:
            self._conn             = sqlite3.connect(self.sqlite_file)
            self._conn.row_factory = sqlite3.Row
            self._conn.isolation_level = None
        except sqlite3.OperationalError, err:
            print 'Error - ' + str(err)
            sys.exit(2) 

    def enqueue(self, data):
        # create the cursor
        _cursor = self._conn.cursor()

        value   = (None, data)
        sql     = 'INSERT INTO ' + self.queue_name + ' VALUES (?, ?)' 
        _cursor.execute(sql, value)

        # commit changest
        self._conn.commit()

    def dequeue(self):
        # create the cursor
        _cursor           = self._conn.cursor()

        sql = 'SELECT * FROM ' + self.queue_name
        _cursor.execute(sql)
        row = _cursor.fetchone()

        if row is not None:
            # delete the row
            value = (row['id'],)
            sql = 'DELETE FROM ' + self.queue_name + ' WHERE id = ?'
            _cursor.execute(sql, value)

            """ Note: rowcount here is always 0 if delete statement has no
                condition"""
            if _cursor.rowcount > 0:
                return row['data']
        else:
            return None

class TDQueueMySQL:

    # param: path to sqlite file, table name
    def __init__(self, param, queue_name):
        self.param      = param
        self.queue_name = queue_name

        # if self.param is not None:
            # override connection details here

        try:
            self._conn = MySQLdb.connect(host="localhost", user="healthopia", passwd="h3althopiA", db="healthopia")
        except MySQLdb.OperationalError, err:
            print 'Error - ' + str(err)
            sys.exit(2) 

    def enqueue(self, data):
        sql     = 'INSERT INTO ' + self.queue_name + ' (data) VALUES (%s)' 
        self._conn.cursor().execute(sql, ((data,)))

        # commit changes
        self._conn.commit()

    def dequeue(self):
        _cursor = self._conn.cursor()
        sql     = 'SELECT id, data FROM ' + self.queue_name
        _cursor.execute(sql)
        row = _cursor.fetchone()

        if row is not None:
            # delete the row
            value = (row[0],)
            sql = 'DELETE FROM ' + self.queue_name + ' WHERE id = %s'
            _cursor.execute(sql, value)

            if _cursor.rowcount > 0:
                self._conn.commit()
                return row[1]
        else:
            return None

# our test
def simple_test():
    x = TDQueueSqlite(':memory:', 'test')

    x._cursor = x._conn.cursor()
    x._cursor.execute('create table test (id integer primary key, data text)')

    for i in range(5):
        if x.enqueue(str(i) + ': this is a test message') is not None:
            print 'enqueue msg: ' + i

    # pdb.set_trace()
    while True:
        y = x.dequeue()

        if y is not None:
            print y
        else:
            break

if __name__ == "__main__":
    simple_test()
